// 增强版数据库连接管理器 - 安全强化和连接可靠性
import pg from 'pg';
import redis from 'redis';
import { MongoClient } from 'mongodb';
import sqlite3 from 'sqlite3';
import { fileURLToPath } from 'url';
import { dirname, join } from 'path';
import { existsSync, mkdirSync } from 'fs';
import enhancedConfig from '../../config/database-enhanced.js';

const { Pool } = pg;
const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

class EnhancedDatabaseManager {
    constructor() {
        this.config = enhancedConfig;
        this.connections = {
            postgresql: null,
            redis: null,
            mongodb: null,
            sqlite: null
        };
        this.connectionStatus = {
            postgresql: 'disconnected',
            redis: 'disconnected',
            mongodb: 'disconnected',
            sqlite: 'disconnected'
        };
        this.retryAttempts = {};
        this.maxRetries = 5;
        this.healthCheckInterval = null;
    }

    // 连接重试机制
    async connectWithRetry(connectFunction, dbName, maxRetries = this.maxRetries) {
        for (let attempt = 1; attempt <= maxRetries; attempt++) {
            try {
                const connection = await connectFunction();
                this.connectionStatus[dbName] = 'connected';
                console.log(`✅ ${dbName} 连接成功`);
                return connection;
            } catch (error) {
                if (attempt < maxRetries) {
                    console.warn(`⚠️ ${dbName} 连接失败 (尝试 ${attempt}/${maxRetries}):`, error.message);
                    await new Promise(resolve => setTimeout(resolve, 2000 * attempt));
                } else {
                    console.error(`❌ ${dbName} 连接最终失败:`, error.message);
                    this.connectionStatus[dbName] = 'failed';
                    throw error;
                }
            }
        }
    }

    // 连接PostgreSQL
    async connectPostgreSQL() {
        const config = this.config.database.postgresql;
        
        const pool = new Pool({
            host: config.host,
            port: config.port,
            database: config.name,
            user: config.user,
            password: config.password,
            
            // 连接池优化
            max: config.maxConnections,
            idleTimeoutMillis: config.idleTimeout,
            connectionTimeoutMillis: config.connectionTimeout,
            
            // SSL配置
            ssl: config.ssl,
            
            // 连接重试
            retry: config.retry
        });

        // 连接池事件监听
        pool.on('connect', (client) => {
            console.log('✅ PostgreSQL: 新客户端连接');
        });

        pool.on('acquire', (client) => {
            console.log('🔗 PostgreSQL: 客户端获取');
        });

        pool.on('remove', (client) => {
            console.log('❌ PostgreSQL: 客户端移除');
        });

        pool.on('error', (err, client) => {
            console.error('💥 PostgreSQL连接池错误:', err);
            this.connectionStatus.postgresql = 'error';
        });

        // 测试连接
        await pool.query('SELECT NOW() as current_time');
        
        return pool;
    }

    // 连接Redis
    async connectRedis() {
        const config = this.config.database.redis;
        
        const client = redis.createClient({
            socket: {
                host: config.host,
                port: config.port,
                keepAlive: config.socket.keepAlive,
                reconnectStrategy: config.socket.reconnectStrategy
            },
            password: config.password,
            database: config.db
        });

        // Redis事件监听
        client.on('connect', () => {
            console.log('✅ Redis: 连接成功');
            this.connectionStatus.redis = 'connected';
        });

        client.on('error', (err) => {
            console.error('💥 Redis错误:', err);
            this.connectionStatus.redis = 'error';
        });

        client.on('reconnecting', () => {
            console.log('🔄 Redis: 重新连接中...');
            this.connectionStatus.redis = 'reconnecting';
        });

        client.on('ready', () => {
            console.log('✅ Redis: 准备就绪');
            this.connectionStatus.redis = 'ready';
        });

        await client.connect();
        await client.ping();
        
        return client;
    }

    // 连接MongoDB
    async connectMongoDB() {
        const config = this.config.database.mongodb;
        
        const client = new MongoClient(config.uri, {
            // 连接池优化
            maxPoolSize: config.poolSize,
            minPoolSize: 5,
            
            // SSL配置
            ssl: config.ssl,
            sslValidate: config.sslValidate,
            sslCA: config.sslCA,
            sslCert: config.sslCert,
            sslKey: config.sslKey,
            
            // 认证配置
            authSource: config.authSource,
            
            // 重试配置
            retryWrites: config.retryWrites,
            retryReads: config.retryReads,
            
            // 超时配置
            connectTimeoutMS: config.connectTimeoutMS,
            socketTimeoutMS: config.socketTimeoutMS,
            serverSelectionTimeoutMS: config.serverSelectionTimeoutMS
        });

        await client.connect();
        
        // 测试连接
        await client.db(config.db).command({ ping: 1 });
        
        return client;
    }

    // 连接SQLite
    async connectSQLite() {
        const config = this.config.database.sqlite;
        const projectRoot = join(__dirname, '..', '..', '..');
        const dbPath = join(projectRoot, config.file);
        
        // 确保数据目录存在
        const dataDir = dirname(dbPath);
        if (!existsSync(dataDir)) {
            mkdirSync(dataDir, { recursive: true });
        }

        return new Promise((resolve, reject) => {
            const db = new sqlite3.Database(dbPath, (err) => {
                if (err) {
                    reject(err);
                } else {
                    // 配置SQLite
                    db.configure("busyTimeout", config.busyTimeout);
                    
                    // 启用外键约束
                    db.run("PRAGMA foreign_keys = ON");
                    
                    // 性能优化
                    db.run(`PRAGMA journal_mode = ${config.journalMode}`);
                    db.run(`PRAGMA synchronous = ${config.synchronous}`);
                    db.run(`PRAGMA cache_size = ${config.cacheSize}`);
                    
                    this.connectionStatus.sqlite = 'connected';
                    resolve(db);
                }
            });
        });
    }

    // 初始化所有数据库连接
    async initialize() {
        console.log('🚀 初始化增强版数据库连接...');
        
        try {
            // 并行连接所有数据库（带重试机制）
            const connectionPromises = [
                this.connectWithRetry(() => this.connectPostgreSQL(), 'postgresql'),
                this.connectWithRetry(() => this.connectRedis(), 'redis'),
                this.connectWithRetry(() => this.connectMongoDB(), 'mongodb'),
                this.connectWithRetry(() => this.connectSQLite(), 'sqlite', 3) // SQLite重试次数较少
            ];

            const results = await Promise.allSettled(connectionPromises);
            
            // 处理连接结果
            results.forEach((result, index) => {
                const dbNames = ['postgresql', 'redis', 'mongodb', 'sqlite'];
                const dbName = dbNames[index];
                
                if (result.status === 'fulfilled') {
                    this.connections[dbName] = result.value;
                    this.connectionStatus[dbName] = 'connected';
                } else {
                    console.error(`❌ ${dbName} 连接失败:`, result.reason.message);
                    this.connectionStatus[dbName] = 'failed';
                }
            });

            // 启动健康检查
            this.startHealthCheck();
            
            console.log('✅ 增强版数据库连接初始化完成');
            this.printConnectionStatus();
            
        } catch (error) {
            console.error('💥 数据库连接初始化失败:', error);
            throw error;
        }
    }

    // 健康检查
    async healthCheck() {
        const healthResults = {};
        
        // PostgreSQL健康检查
        try {
            if (this.connections.postgresql) {
                const result = await this.connections.postgresql.query('SELECT NOW() as current_time');
                healthResults.postgresql = {
                    status: 'healthy',
                    timestamp: result.rows[0].current_time,
                    connectionCount: this.connections.postgresql.totalCount
                };
            } else {
                healthResults.postgresql = { status: 'disconnected' };
            }
        } catch (error) {
            healthResults.postgresql = { status: 'unhealthy', error: error.message };
        }

        // Redis健康检查
        try {
            if (this.connections.redis && this.connections.redis.isOpen) {
                const pong = await this.connections.redis.ping();
                healthResults.redis = {
                    status: 'healthy',
                    response: pong
                };
            } else {
                healthResults.redis = { status: 'disconnected' };
            }
        } catch (error) {
            healthResults.redis = { status: 'unhealthy', error: error.message };
        }

        // MongoDB健康检查
        try {
            if (this.connections.mongodb) {
                await this.connections.mongodb.db(this.config.database.mongodb.db).command({ ping: 1 });
                healthResults.mongodb = { status: 'healthy' };
            } else {
                healthResults.mongodb = { status: 'disconnected' };
            }
        } catch (error) {
            healthResults.mongodb = { status: 'unhealthy', error: error.message };
        }

        // SQLite健康检查
        try {
            if (this.connections.sqlite) {
                await new Promise((resolve, reject) => {
                    this.connections.sqlite.get("SELECT 1 as test", (err, row) => {
                        if (err) reject(err);
                        else resolve(row);
                    });
                });
                healthResults.sqlite = { status: 'healthy' };
            } else {
                healthResults.sqlite = { status: 'disconnected' };
            }
        } catch (error) {
            healthResults.sqlite = { status: 'unhealthy', error: error.message };
        }

        return healthResults;
    }

    // 启动定期健康检查
    startHealthCheck() {
        if (this.healthCheckInterval) {
            clearInterval(this.healthCheckInterval);
        }

        this.healthCheckInterval = setInterval(async () => {
            try {
                const health = await this.healthCheck();
                
                // 更新连接状态
                Object.keys(health).forEach(dbName => {
                    this.connectionStatus[dbName] = health[dbName].status;
                });
                
                // 如果有数据库不健康，尝试重新连接
                const unhealthyDbs = Object.keys(health).filter(
                    dbName => health[dbName].status === 'unhealthy'
                );
                
                if (unhealthyDbs.length > 0) {
                    console.log('🔄 检测到不健康的数据库，尝试重新连接:', unhealthyDbs.join(', '));
                    this.reconnectUnhealthyDbs(unhealthyDbs);
                }
                
            } catch (error) {
                console.error('💥 健康检查失败:', error);
            }
        }, this.config.monitoring.healthCheckInterval);
    }

    // 重新连接不健康的数据库
    async reconnectUnhealthyDbs(dbNames) {
        for (const dbName of dbNames) {
            try {
                console.log(`🔄 重新连接 ${dbName}...`);
                
                // 关闭现有连接
                await this.closeConnection(dbName);
                
                // 重新连接
                const connectFunction = {
                    'postgresql': () => this.connectPostgreSQL(),
                    'redis': () => this.connectRedis(),
                    'mongodb': () => this.connectMongoDB(),
                    'sqlite': () => this.connectSQLite()
                }[dbName];
                
                if (connectFunction) {
                    this.connections[dbName] = await this.connectWithRetry(connectFunction, dbName, 3);
                    console.log(`✅ ${dbName} 重新连接成功`);
                }
                
            } catch (error) {
                console.error(`❌ ${dbName} 重新连接失败:`, error.message);
            }
        }
    }

    // 关闭单个连接
    async closeConnection(dbName) {
        try {
            const connection = this.connections[dbName];
            if (connection) {
                switch (dbName) {
                    case 'postgresql':
                        await connection.end();
                        break;
                    case 'redis':
                        if (connection.isOpen) {
                            await connection.quit();
                        }
                        break;
                    case 'mongodb':
                        await connection.close();
                        break;
                    case 'sqlite':
                        connection.close();
                        break;
                }
                this.connections[dbName] = null;
                this.connectionStatus[dbName] = 'disconnected';
            }
        } catch (error) {
            console.error(`❌ 关闭 ${dbName} 连接失败:`, error.message);
        }
    }

    // 优雅关闭所有连接
    async gracefulShutdown() {
        console.log('🛑 正在关闭增强版数据库连接...');
        
        // 停止健康检查
        if (this.healthCheckInterval) {
            clearInterval(this.healthCheckInterval);
        }
        
        // 关闭所有连接
        const closePromises = Object.keys(this.connections).map(dbName => 
            this.closeConnection(dbName)
        );
        
        await Promise.allSettled(closePromises);
        
        console.log('✅ 增强版数据库连接已关闭');
    }

    // 打印连接状态
    printConnectionStatus() {
        console.log('\n📊 增强版数据库连接状态:');
        console.log('='.repeat(50));
        
        Object.keys(this.connectionStatus).forEach(dbName => {
            const status = this.connectionStatus[dbName];
            const icon = status === 'connected' ? '✅' : 
                        status === 'reconnecting' ? '🔄' : '❌';
            console.log(`${icon} ${dbName}: ${status}`);
        });
        
        console.log('='.repeat(50));
    }

    // 获取连接统计信息
    async getConnectionStats() {
        const stats = {};
        
        if (this.connections.postgresql) {
            stats.postgresql = {
                totalCount: this.connections.postgresql.totalCount,
                idleCount: this.connections.postgresql.idleCount,
                waitingCount: this.connections.postgresql.waitingCount
            };
        }
        
        if (this.connections.redis) {
            stats.redis = {
                isOpen: this.connections.redis.isOpen,
                connected: this.connections.redis.connected
            };
        }
        
        return stats;
    }
}

// 创建全局数据库管理器实例
const enhancedDatabaseManager = new EnhancedDatabaseManager();

export default enhancedDatabaseManager;
export { EnhancedDatabaseManager };